package com.gopay.cron.service.cloud.impl;

import java.io.File;
import java.io.IOException;
import java.math.BigDecimal;
import java.math.RoundingMode;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.HashMap;
import java.util.HashSet;
import java.util.List;
import java.util.Map;
import java.util.Set;

import javax.annotation.Resource;

import org.apache.commons.lang3.StringUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.stereotype.Service;

import com.gopay.common.constants.trans.PayChannel;
import com.gopay.common.cps.dao.order.CpsGeneralMainOrderQueryDAO;
import com.gopay.common.datasource.DBType;
import com.gopay.common.datasource.DbContextHolder;
import com.gopay.common.domain.acps.AcctProd;
import com.gopay.common.domain.acps.AcpsAcctMast;
import com.gopay.common.domain.cloud.CloudCustAcctRel;
import com.gopay.common.domain.cps.CpsGenMainOrder;
import com.gopay.common.order.dao.CommonQueryDAO;
import com.gopay.common.user.dao.CloudCustAcctRelDao;
import com.gopay.common.util.DateUtils;
import com.gopay.common.util.FtpUtil;
import com.gopay.cron.service.cloud.EShopAccountGenFtpFileService;
import com.gopay.cron.service.cloud.gen.EShopCloudOrderFileGenHelper;
import com.gopay.cron.service.cloud.gen.bean.CloudRecvFileInfo;
import com.gopay.cron.service.cloud.gen.bean.CloudRefundFileInfo;
import com.gopay.cron.service.cloud.gen.bean.CloudSplitFileInfo;
import com.gopay.cron.service.cloud.gen.bean.CloudSplitRefundFileInfo;
import com.gopay.cron.web.vo.CloudAccountStatementVO;
import com.gopay.cron.web.vo.CloudSplitFileVO;
import com.gopay.cron.web.vo.CloudSplitRefundFileVO;
import com.gopay.scheduler.common.ReconciliationFileType;
import com.gopay.scheduler.dao.CloudAcctQueryDAO;

/** 
 * @ClassName: EShopAccountGenFtpFileServiceImpl 
 * @Description: E商通生成对账文件服务实现类
 * @author liuxuyang
 * @date 2018年3月19日 下午4:21:24  
 */

@Service("eShopAccountGenFtpFileService")
public class EShopAccountGenFtpFileServiceImpl implements EShopAccountGenFtpFileService {

	private Logger logger = LoggerFactory.getLogger(this.getClass());
	
	@Resource(name="cloudCustAcctRelDao")
    private CloudCustAcctRelDao cloudCustAcctRelDao;
	
	@Resource(name = "cloudReconciliationAcctQueryDao")
	private CloudAcctQueryDAO cloudAcctQueryDAO;
	
	@Resource(name = "cloudFtpConfig")
    private Map<String, String> cloudFtpConfig;
	
	@Resource(name = "commonQueryDAO")
    private CommonQueryDAO commonQueryDAO;
	
	@Resource(name = "cpsGeneralMainOrderQueryDAO")
    private CpsGeneralMainOrderQueryDAO cpsGeneralMainOrderQueryDAO;
	
	@Override
	public void genFile(String filePath, String checkDate,Set<String> custIdSet) {
		List<AcpsAcctMast> acctsList = cloudAcctQueryDAO.getCloudAccts();
        if (null == acctsList || acctsList.size() == 0) {
            logger.info("job end：没有配置需要生成对账文件的custId，job结束");
            return;
        }
        
		//logger.info("查询到的云账户总账户信息:{}",JSON.toJSONString(acctsList));
		for (AcpsAcctMast acct : acctsList) {
			String custId = acct.getCustId();
			String acctId = acct.getAcctId();
			
			//排除掌合的账户
			if (custIdSet.contains(custId)) {
				continue;
			}
			
			//与商城签约的所有
			List<CloudCustAcctRel> acctRels = cloudCustAcctRelDao.findSubCloudAccts(custId, acctId);
			//1.生成对账文件并上传FTP
			EShopCloudOrderFileGenHelper helper = prepareData(checkDate, acct, acctRels);
			StringBuilder genPath = new StringBuilder();
	        genPath.append(filePath).append(custId).append(File.separator).append(checkDate).append(File.separator);
	        String genPathString = genPath.toString();
	        helper.setFilePath(genPathString);
	        helper.setProdCode(acct.getProdCode().CLOUD.name());
	        helper.setCheckDate(checkDate.replace("-",""));
	        String acctIdSub = acctId.substring(acctId.length() - 4,acctId.length());
	        helper.setAcctId(acctIdSub);
	        
	        try {
				helper.genFtpFile();
				
				Set<String> fileNameSet = new HashSet<>();
				String recvFileName = checkDate.replace("-", "") + "-" + acctIdSub + "-" + CloudRecvFileInfo.fileFtpName + ".csv";
                fileNameSet.add(recvFileName);
                String refundFileName = checkDate.replace("-", "") + "-" + acctIdSub + "-" + CloudRefundFileInfo.fileFtpName + ".csv";
                fileNameSet.add(refundFileName);
				String splitFileName = checkDate.replace("-", "") + "-" + acctIdSub + "-" + CloudSplitFileInfo.fileFtpName + ".csv";
                fileNameSet.add(splitFileName);
				String splitRefundFileName = checkDate.replace("-", "") + "-" + acctIdSub + "-" + CloudSplitRefundFileInfo.fileFtpName + ".csv";
                fileNameSet.add(splitRefundFileName);
                
                uploadFileToFtp(genPathString,fileNameSet);
			} catch (IOException e) {
				logger.error("生成文件出错",e);
			}
	        
			//2.清除三十天之前的文件
			//删除文件的路径
	        String deleteFilePath = filePath + custId + File.separator;
	        Calendar calendar = Calendar.getInstance();
	        calendar.add(Calendar.DATE, -30);
	        String rmd = new SimpleDateFormat("yyyy-MM-dd").format(calendar.getTime());
	        logger.error("开始清除三十天之前的文件,清除的日期为：" + rmd);
	        File path = new File(deleteFilePath + rmd);
	        deleteFile(path);
		}
	}
	
	/**
	 * @Description: 构建对账文件数据
	 * @param checkDate
	 * @param acct
	 * @param acctRels
	 * @return 
	 * @author liuxuyang
	 */
	private EShopCloudOrderFileGenHelper prepareData(String checkDate,AcpsAcctMast acct,List<CloudCustAcctRel> acctRels) {
		DbContextHolder.setDbType(DBType.DS_ONLY_READ.value);
        String beginTime = checkDate + "00:00:00";
        String endTime = checkDate + "23:59:59";
        
        EShopCloudOrderFileGenHelper helper = new EShopCloudOrderFileGenHelper();
        
        //收款汇总
        CloudAccountStatementVO recvTotal = getTotal(acct, acctRels, beginTime, endTime,ReconciliationFileType.RECV);
        if (null != recvTotal) {
        	helper.addFtpFirstRecvFileLineCloud(recvTotal);
        }
        //收款明细
        List<CloudAccountStatementVO> recvDetailsList = getRecvDetails(acct, acctRels, beginTime, endTime);
        if (null != recvDetailsList && recvDetailsList.size() > 0) {
        	for (CloudAccountStatementVO vo : recvDetailsList) {
        		helper.addFtpSecondRecvFileLineCloud(vo);
        	}
        }
        //退款汇总
        CloudAccountStatementVO refundTotal = getTotal(acct, acctRels, beginTime, endTime,ReconciliationFileType.REFUND);
        if (null != refundTotal) {
        	helper.addFtpFirstRefundFileLineCloud(refundTotal);
        }
        //退款明细
        List<CloudAccountStatementVO> refundDetailsList = getRefundDetails(acct, acctRels, beginTime, endTime);
        if (null != refundDetailsList && refundDetailsList.size() > 0) {
        	for (CloudAccountStatementVO vo : refundDetailsList) {
        		helper.addFtpSecondRefundFileLineCloud(vo);
        	}
        }
        //分账汇总
        CloudAccountStatementVO splitTotal = getTotal(acct, acctRels, beginTime, endTime,ReconciliationFileType.SPLIT);
        if (null != splitTotal) {
        	helper.addFtpFirstSplitFileLineCloud(splitTotal);
        }
        //分账明细
        List<CloudSplitFileVO> splitDetailsList = getSplitDetails(acct, acctRels, beginTime, endTime);
        if (null != splitDetailsList && splitDetailsList.size() > 0) {
        	for (CloudSplitFileVO vo : splitDetailsList) {
        		helper.addFtpSecondSplitFileLineCloud(vo);
        	}
        }
        //分账退款汇总
        CloudAccountStatementVO splitRefundTotal = getTotal(acct, acctRels, beginTime, endTime,ReconciliationFileType.SPLIT_REFUND);
        if (null != splitRefundTotal) {
        	helper.addFtpFirstSplitRefundFileLineCloud(splitRefundTotal);
        }
        //分账退款明细
        List<CloudSplitRefundFileVO> splitRefundDetailsList = getSplitRefundDetails(acct, acctRels, beginTime, endTime);
        if (null != splitRefundDetailsList && splitRefundDetailsList.size() > 0) {
        	for (CloudSplitRefundFileVO vo : splitRefundDetailsList) {
        		helper.addFtpSecondSplitRefundFileLineCloud(vo);
        	}
        }
        
        return helper;
	}
	
	/**
	 * @Description: 获取汇总数据
	 * @param acct
	 * @param acctRels
	 * @param beginTime
	 * @param endTime
	 * @return 
	 * @author liuxuyang
	 */
	private CloudAccountStatementVO getTotal(AcpsAcctMast acct,
			List<CloudCustAcctRel> acctRels,String beginTime,String endTime,String type) {
		CloudAccountStatementVO vo = new CloudAccountStatementVO();
		vo.setGopayAcct(acct.getAcctId());
		
		String custId = acct.getCustId();
		String acctId = acct.getAcctId();
		List<Object[]> total = null;
		if (StringUtils.equals(type, ReconciliationFileType.SPLIT_REFUND)) {
			total = commonQueryDAO.findBySql(buildSplitRefundTotalSql(custId, acctId, beginTime, endTime));
		} else if (StringUtils.equals(type, ReconciliationFileType.SPLIT)) {
			total = commonQueryDAO.findBySql(buildSplitTotalSql(custId, acctId, beginTime, endTime));
		} else if (StringUtils.equals(type, ReconciliationFileType.RECV)) {
			total = commonQueryDAO.findBySql(buildRecvTotalSql(custId, acctId, beginTime, endTime));
		} else if (StringUtils.equals(type, ReconciliationFileType.REFUND)) {
			total = commonQueryDAO.findBySql(buildRefundTotalSql(custId, acctId, beginTime, endTime));
		}
		
		if (null != total) {
			vo.setTotalCount((total.get(0)[0]).toString());
			if(total.get(0)[1] != null){
				vo.setTotalTxnAmt(((BigDecimal) total.get(0)[1]).setScale(2,RoundingMode.HALF_UP).toString());
            }else{
            	vo.setTotalTxnAmt("0.00");
            }
            if(total.get(0)[2] != null){
            	vo.setTotalFeeAmt(((BigDecimal) total.get(0)[2]).setScale(2,RoundingMode.HALF_UP).toString());
            }else{
            	vo.setTotalFeeAmt("0.00");
            }
		} else {
			vo.setTotalCount("0");
			vo.setTotalTxnAmt("0.00");
			vo.setTotalFeeAmt("0.00");
		}
		
		if (null != acctRels && acctRels.size() > 0) {
			for (CloudCustAcctRel rel : acctRels) {
				custId = rel.getCustId();
				acctId = rel.getAcctId();
				if (StringUtils.equals(type, ReconciliationFileType.SPLIT_REFUND)) {
					total = commonQueryDAO.findBySql(buildSplitRefundTotalSql(custId, acctId, beginTime, endTime));
				} else if (StringUtils.equals(type, ReconciliationFileType.SPLIT)) {
					total = commonQueryDAO.findBySql(buildSplitTotalSql(custId, acctId, beginTime, endTime));
				}  else if (StringUtils.equals(type, ReconciliationFileType.RECV)) {
					total = commonQueryDAO.findBySql(buildRecvTotalSql(custId, acctId, beginTime, endTime));
				} else if (StringUtils.equals(type, ReconciliationFileType.REFUND)) {
					total = commonQueryDAO.findBySql(buildRefundTotalSql(custId, acctId, beginTime, endTime));
				}
				if (null != total && total.size() > 0) {
					if(total.get(0)[0] != null){
						long oriTotal = Long.parseLong(vo.getTotalCount());
						long curTotal = Long.parseLong((total.get(0)[0]).toString());
						vo.setTotalCount(String.valueOf(oriTotal + curTotal));
					}
					if(total.get(0)[1] != null){
						BigDecimal oriTxnAmt = new BigDecimal(vo.getTotalTxnAmt());
						BigDecimal curTxnAmt = new BigDecimal((total.get(0)[1]).toString());
						vo.setTotalTxnAmt((oriTxnAmt.add(curTxnAmt)).setScale(2,RoundingMode.HALF_UP).toString());
		            }
		            if(total.get(0)[2] != null){
		            	BigDecimal oriFeeAmt = new BigDecimal(vo.getTotalFeeAmt());
		            	BigDecimal curFeeAmt = new BigDecimal((total.get(0)[2]).toString());
		            	vo.setTotalFeeAmt((oriFeeAmt.add(curFeeAmt)).setScale(2,RoundingMode.HALF_UP).toString());
		            }
				}
			}
		}
		
		return vo;
	}
	
	/**
	 * @Description: 获取分账退款明细数据
	 * @param acct
	 * @param acctRels
	 * @param beginTime
	 * @param endTime
	 * @return 
	 * @author liuxuyang
	 */
	private List<CloudSplitRefundFileVO> getSplitRefundDetails(AcpsAcctMast acct,
			List<CloudCustAcctRel> acctRels,String beginTime,String endTime) {
		List<CloudSplitRefundFileVO> list = new ArrayList<CloudSplitRefundFileVO>();
		
		String merCustId = acct.getCustId();
		String acctId = acct.getAcctId();
		List<Object[]> splitRefundDetail = commonQueryDAO.findBySql(buildSplitRefundDetailsSql(merCustId, acctId, beginTime, endTime));
		if (null != acctRels && acctRels.size() > 0) {
			if (null == splitRefundDetail) {
				splitRefundDetail = new ArrayList<Object[]>();
			}
			for (CloudCustAcctRel rel : acctRels) {
				String custId = rel.getCustId();
				acctId = rel.getAcctId();
				List<Object[]> splitRefundDetailRel = commonQueryDAO.findBySql(buildSplitRefundDetailsSql(custId, acctId, beginTime, endTime));
				if (null != splitRefundDetail && splitRefundDetail.size() > 0) {
					splitRefundDetail.addAll(splitRefundDetailRel);
				}
			}
		}
		
		if (null != splitRefundDetail && splitRefundDetail.size() > 0) {
			for (Object[] obj : splitRefundDetail) {
				CloudSplitRefundFileVO vo = new CloudSplitRefundFileVO();
				vo.setMerCustId(merCustId);
				if(obj[0] != null){
					vo.setSplitRefundOrderId(obj[0].toString());
				}
				if(obj[1] != null){
					vo.setSplitRefundAmt(((BigDecimal) obj[1]).setScale(2,RoundingMode.HALF_UP).toString());
				}
				if(obj[2] != null){
					vo.setReFundOrderId(obj[2].toString());
				}
				if(obj[3] != null){
					vo.setReFundAmt(((BigDecimal) obj[3]).setScale(2,RoundingMode.HALF_UP).toString());
				}
				if(obj[4] != null){
					vo.setOriMerOrderId(obj[4].toString());
				}
				if(obj[5] != null){
					vo.setOriOrderAmt(((BigDecimal) obj[5]).setScale(2,RoundingMode.HALF_UP).toString());
				}
				if(obj[6] != null){
                    vo.setSplitCreateTime(obj[6].toString());
                }
				if(obj[7] != null){
                    vo.setSplitFinishTime(DateUtils.format((Date) obj[7],"yyyyMMddHHmmss"));
                }
				list.add(vo);
			}
		}
		
		return list;
	}
	
	/**
	 * @Description: 获取分账明细 
	 * @param acct
	 * @param acctRels
	 * @param beginTime
	 * @param endTime
	 * @return 
	 * @author liuxuyang
	 */
	private List<CloudSplitFileVO> getSplitDetails(AcpsAcctMast acct,
			List<CloudCustAcctRel> acctRels,String beginTime,String endTime) {
		List<CloudSplitFileVO> list = new ArrayList<CloudSplitFileVO>();
		
		String merCustId = acct.getCustId();
		String acctId = acct.getAcctId();
		List<Object[]> splitDetail = commonQueryDAO.findBySql(buildSplitDetailsSql(merCustId, acctId, beginTime, endTime,AcctProd.CLOUD.name()));
		if (null != acctRels && acctRels.size() > 0) {
			if (null == splitDetail) {
				splitDetail = new ArrayList<Object[]>();
			}
			for (CloudCustAcctRel rel : acctRels) {
				String custId = rel.getCustId();
				acctId = rel.getAcctId();
				List<Object[]> splitDetailRel = commonQueryDAO.findBySql(buildSplitDetailsSql(custId, acctId, beginTime, endTime,AcctProd.SUBCLOUD.name()));
				if (null != splitDetailRel && splitDetailRel.size() > 0) {
					splitDetail.addAll(splitDetailRel);
				}
			}
		}
		
		if (null != splitDetail && splitDetail.size() > 0) {
			for (Object[] obj : splitDetail) {
				CloudSplitFileVO vo = new CloudSplitFileVO();
				vo.setMerCustId(merCustId);
				if(obj[0] != null){
					vo.setSplitAcctId(obj[0].toString());
				}
				if(obj[1] != null){
					vo.setSplitOrderId(obj[1].toString());
				}
				if(obj[2] != null){
					vo.setSplitOrderAmt(((BigDecimal) obj[2]).setScale(2,RoundingMode.HALF_UP).toString());
				}
				if(obj[3] != null){
					vo.setOriMerOrderId(obj[3].toString());
				}
				if(obj[4] != null){
					vo.setOriOrderAmt(((BigDecimal) obj[4]).setScale(2,RoundingMode.HALF_UP).toString());
				}
				if(obj[5] != null){
                    vo.setSplitCreateTime(obj[5].toString());
                }
				if(obj[6] != null){
                    vo.setSplitFinishTime(DateUtils.format((Date) obj[6],"yyyyMMddHHmmss"));
                }
				
				list.add(vo);
			}
		}
		
		return list;
	}
	
	/**
	 * @Description: 获取收款类明细 
	 * @param acct
	 * @param acctRels
	 * @param beginTime
	 * @param endTime
	 * @return 
	 * @author liuxuyang
	 */
	private List<CloudAccountStatementVO> getRecvDetails(AcpsAcctMast acct,
			List<CloudCustAcctRel> acctRels,String beginTime,String endTime) {
		List<CloudAccountStatementVO> list = new ArrayList<CloudAccountStatementVO>();
		
		String merCustId = acct.getCustId();
		String acctId = acct.getAcctId();
		List<Object[]> recvDetail = commonQueryDAO.findBySql(buildRecvDetailsSql(merCustId, acctId, beginTime, endTime));
		if (null != acctRels && acctRels.size() > 0) {
			if (null == recvDetail) {
				recvDetail = new ArrayList<Object[]>();
			}
			for (CloudCustAcctRel rel : acctRels) {
				String custId = rel.getCustId();
				acctId = rel.getAcctId();
				List<Object[]> recvDetailRel = commonQueryDAO.findBySql(buildRecvDetailsSql(custId, acctId, beginTime, endTime));
				if (null != recvDetailRel && recvDetailRel.size() > 0) {
					recvDetail.addAll(recvDetailRel);
				}
			}
		}
		
		if (null != recvDetail && recvDetail.size() > 0) {
			for (Object[] obj : recvDetail) {
				CloudAccountStatementVO vo = new CloudAccountStatementVO();
                vo.setGopayOrderId((String) obj[0]);
                vo.setMerOrderId((String) obj[1]);
                vo.setGopayIntTxnCd(translateGopayIntTxnCd((String) obj[2]));
                vo.setReqTime(DateUtils.format((Date) obj[3],"yyyyMMddHHmmss"));
                vo.setTranFinishTime(DateUtils.format((Date) obj[4],"yyyyMMddHHmmss"));
                vo.setMerTxnAmt(((BigDecimal) obj[5]).setScale(2,RoundingMode.HALF_UP).toString());
                vo.setOrderFeeAmt(((BigDecimal) obj[6]).setScale(2, RoundingMode.HALF_UP).toString());
                vo.setPayChannel(PayChannel.translate((String) obj[7]));
                vo.setSplitType(null == obj[8] ?"":this.translateSplitType(obj[8].toString()));
                vo.setSplitStatus(null == obj[9] ?"":this.translateSplitStatus(obj[9].toString()));
                list.add(vo);
			}
		}
		
		return list;
	}
	
	/**
	 * @Description: 获取退款明细
	 * @param acct
	 * @param acctRels
	 * @param beginTime
	 * @param endTime
	 * @return 
	 * @author liuxuyang
	 */
	private List<CloudAccountStatementVO> getRefundDetails(AcpsAcctMast acct,
			List<CloudCustAcctRel> acctRels,String beginTime,String endTime) {
		List<CloudAccountStatementVO> list = new ArrayList<CloudAccountStatementVO>();
		
		String merCustId = acct.getCustId();
		String acctId = acct.getAcctId();
		List<Object[]> refundDetail = commonQueryDAO.findBySql(buildRefundDetailsSql(merCustId, acctId, beginTime, endTime));
		if (null != acctRels && acctRels.size() > 0) {
			if (null == refundDetail) {
				refundDetail = new ArrayList<Object[]>();
			}
			for (CloudCustAcctRel rel : acctRels) {
				String custId = rel.getCustId();
				acctId = rel.getAcctId();
				List<Object[]> refundDetailRel = commonQueryDAO.findBySql(buildRefundDetailsSql(custId, acctId, beginTime, endTime));
				if (null != refundDetailRel && refundDetailRel.size() > 0) {
					refundDetail.addAll(refundDetailRel);
				}
			}
		}
		
		if (null != refundDetail && refundDetail.size() > 0) {
			for (Object[] obj : refundDetail) {
				CloudAccountStatementVO vo = new CloudAccountStatementVO();
                vo.setGopayOrderId((String) obj[0]);
                vo.setMerOrderId((String) obj[1]);
                if(obj[2] != null){
                    String origGopayOrderId = (String) obj[2]; //原交易国付宝订单号
                    vo.setOrigGopayOrderId(origGopayOrderId);
                    CpsGenMainOrder cpsGenMainOrder =cpsGeneralMainOrderQueryDAO.findByGopayOrderId(origGopayOrderId);
                    if(cpsGenMainOrder != null){
                        vo.setOrigMerOrderId(cpsGenMainOrder.getMerOrderId());
                        vo.setOrigMerTxnAmt((cpsGenMainOrder.getMerTxnAmt()).setScale(2,RoundingMode.HALF_UP).toString());
                    }
                }
                vo.setGopayIntTxnCd(translateGopayIntTxnCd((String) obj[3]));
                vo.setReqTime(DateUtils.format((Date) obj[4],"yyyyMMddHHmmss"));
                if(obj[5] != null){
                    vo.setTranFinishTime(DateUtils.format((Date) obj[5],"yyyyMMddHHmmss"));
                } else {
                	vo.setTranFinishTime("");
                }
                vo.setMerTxnAmt(((BigDecimal) obj[6]).setScale(2,RoundingMode.HALF_UP).toString());
                vo.setOrderFeeAmt(((BigDecimal) obj[7]).setScale(2, RoundingMode.HALF_UP).toString());
                list.add(vo);
			}
			
			//添加分账退款订单数据
			if (list.size() > 0) {
				this.addSplitRefundOrders(list);
			}
		}
		
		return list;
	}
	
	/**
	 * @Description: 添加分账退款订单 
	 * @param list 
	 * @author liuxuyang
	 */
	private void addSplitRefundOrders(List<CloudAccountStatementVO> list) {
		StringBuilder sql = new StringBuilder();
		Map<String, Object> param = new HashMap<String, Object>();
		
		sql.append(" select t.gopay_order_id,");
        sql.append(" t.mer_order_id,");
        sql.append(" o.other_recv_info as relevance_gopay_order_id,");
        sql.append(" t.gopay_int_txn_cd,  ");
        sql.append(" t.mer_txn_tm,       ");
        sql.append(" t.tran_finish_time, ") ;
        sql.append(" t.mer_txn_amt,");
        sql.append(" (case when t.order_fee_payer ='01' then t.order_real_fee_amt else 0.00 end) as order_real_fee_amt,");
        sql.append(" t.pay_channel       ");
        sql.append(" from cps_gen_main_order t ");
        sql.append(" left join cps_gen_assist_order o on o.gopay_order_id = t.gopay_order_id ");
        sql.append(" where t.gopay_order_id in (select a.gopay_order_id from cps_gen_assist_order a where a.other_recv_info in (:ids)) ");
        
        //String condition = "";
        List<String> conArr = new ArrayList<String>();
        Map<String,CloudAccountStatementVO> splitRefundMap = new HashMap<String,CloudAccountStatementVO>();
        for (int i=0;i<list.size();i++) {
        	CloudAccountStatementVO con = list.get(i);
        	//condition += "'" + con.getGopayOrderId() + "',";
        	conArr.add(con.getGopayOrderId());
        	splitRefundMap.put(con.getGopayOrderId(), con);
        	
        	if (i+1 %1000 == 0 || i+1 == list.size()) {
        		//因为oracle sql 查询 in条件查询超过1000 个会报错
        		//故在此做判断，如果条件超过1000个，分批查询
        		//condition = condition.substring(0, condition.length() -1);
        		param.put("ids", conArr.toArray());
                
                List<Object[]> splitRefunds = commonQueryDAO.findBySql(sql.toString(),param);
                if (null != splitRefunds && splitRefunds.size() > 0) {
                	for (Object[] obj : splitRefunds) {
                		CloudAccountStatementVO vo = new CloudAccountStatementVO();
                        vo.setGopayOrderId((String) obj[0]);
                        vo.setMerOrderId((String) obj[1]);
                        if(obj[2] != null){
                            String refundGopayOrderId = (String) obj[2]; //原退款订单号
                            vo.setOrigGopayOrderId(splitRefundMap.get(refundGopayOrderId).getOrigGopayOrderId());
                            vo.setOrigMerOrderId(splitRefundMap.get(refundGopayOrderId).getOrigMerOrderId());
                            vo.setOrigMerTxnAmt(splitRefundMap.get(refundGopayOrderId).getOrigMerTxnAmt());
                        }
                        vo.setGopayIntTxnCd(translateGopayIntTxnCd((String) obj[3]));
                        vo.setReqTime(DateUtils.format((Date) obj[4],"yyyyMMddHHmmss"));
                        if(obj[5] != null){
                            vo.setTranFinishTime(DateUtils.format((Date) obj[5],"yyyyMMddHHmmss"));
                        } else {
                        	vo.setTranFinishTime("");
                        }
                        vo.setMerTxnAmt(((BigDecimal) obj[6]).setScale(2,RoundingMode.HALF_UP).toString());
                        vo.setOrderFeeAmt(((BigDecimal) obj[7]).setScale(2, RoundingMode.HALF_UP).toString());
                        list.add(vo);
                	}
                }
                
        		//condition = "";
                conArr.clear();
        		splitRefundMap.clear();
        	} 
        	
        }
        
	}
	
	/**
	 * @Description: 构建分账退款汇总SQL
	 * @param custId
	 * @param acctId
	 * @param beginTime
	 * @param endTime
	 * @return 
	 * @author liuxuyang
	 */
	private String buildSplitRefundTotalSql(String custId, String acctId, String beginTime, String endTime) {
		StringBuilder sql = new StringBuilder();
		
		sql.append(" select count(*),");
        sql.append(" sum(c.mer_txn_amt) as totalAmt,");
        sql.append(" sum(case when c.order_fee_payer ='01' then c.total_order_fee_amt else 0.00 end) as totalFeeAmt  ");
        sql.append(" from cps_gen_main_order c       ");
        sql.append(" where c.gopay_int_txn_cd in ('01202')  and c.txn_sta_cd ='20000'") ;
        sql.append(" and c.pay_acct = '").append(acctId).append("'");
        sql.append(" and c.pay_cust_id = '").append(custId).append("'");
        sql.append(" and c.gateway_source = '22'       ");
        sql.append(" and c.mer_txn_tm >= ");
        sql.append(" to_timestamp('").append(beginTime).append("','yyyy-MM-dd HH24:MI:ss')");
        sql.append(" and c.mer_txn_tm <= ");
        sql.append(" to_timestamp('").append(endTime).append("','yyyy-MM-dd HH24:MI:ss')");
        
        //logger.info(">>>>>>>>>>>查询分账退款汇总SQL:{}",sql.toString());
        
        return sql.toString();
	}
	
	/**
	 * @Description: 分账退款明细 
	 * @param custId
	 * @param acctId
	 * @param beginTime
	 * @param endTime
	 * @return 
	 * @author liuxuyang
	 */
	private String buildSplitRefundDetailsSql(String custId, String acctId, String beginTime, String endTime) {
		StringBuilder sql = new StringBuilder();
		
		sql.append(" select t1.splitRefundOrderId,");
        sql.append(" t1.splitRefundAmt,");
        sql.append(" t1.reFundOrderId,  ");
        sql.append(" t1.reFundAmt,      ");
        sql.append(" mo2.gopay_order_id as oriMerOrderId, ") ;
        sql.append(" mo2.mer_txn_amt as oriOrderAmt,");
        sql.append(" d.other_recv_time as splitCreateTime,");
        sql.append(" d.sub_acct_finish_time as splitFinishTime  ");
        sql.append(" from (     ");
        sql.append(" select t.splitRefundOrderId,t.splitRefundAmt,mo1.gopay_order_id as reFundOrderId,mo1.mer_txn_amt as reFundAmt,mo1.relevance_gopay_order_id ") ;
        sql.append(" from (     ");
        sql.append(" select mo.gopay_order_id as splitRefundOrderId, mo.mer_txn_amt as splitRefundAmt,a.other_recv_info     ");
        sql.append(" from cps_gen_main_order mo     ");
        sql.append(" left join cps_gen_assist_order a on mo.gopay_order_id = a.gopay_order_id      ");
        sql.append(" where mo.gopay_int_txn_cd = '01202'     ");
        sql.append(" and mo.txn_sta_cd = '20000'     ");
        sql.append(" and mo.pay_acct = '").append(acctId).append("'");
        sql.append(" and mo.pay_cust_id = '").append(custId).append("'");
        sql.append(" and mo.gateway_source = '22'       ");
        sql.append(" and mo.mer_txn_tm >= ");
        sql.append(" to_timestamp('").append(beginTime).append("','yyyy-MM-dd HH24:MI:ss')");
        sql.append(" and mo.mer_txn_tm <= ");
        sql.append(" to_timestamp('").append(endTime).append("','yyyy-MM-dd HH24:MI:ss')");
        sql.append(" ) t left join cps_gen_main_order mo1 on t.other_recv_info = mo1.gopay_order_id    ");
        sql.append(" ) t1 left join cps_gen_main_order mo2 on t1.relevance_gopay_order_id = mo2.gopay_order_id     ");
        sql.append(" left join cps_gen_receipt_order d on t1.relevance_gopay_order_id = d.gopay_order_id     ");
		//logger.info(">>>>>>>>>>>查询分账退款明细SQL:{}",sql.toString());
        
        return sql.toString();
	}
	
	/**
	 * @Description: 构建分账汇总SQL 
	 * @param custId
	 * @param acctId
	 * @param beginTime
	 * @param endTime
	 * @return 
	 * @author liuxuyang
	 */
	private String buildSplitTotalSql(String custId, String acctId, String beginTime, String endTime) {
        StringBuilder sql = new StringBuilder();
        sql.append(" select count(*),");
        sql.append(" sum(c.mer_txn_amt) as totalAmt,");
        sql.append(" sum(case when c.order_fee_payer ='02' then c.total_order_fee_amt else 0.00 end) as totalFeeAmt ");
        sql.append(" from cps_gen_main_order c       ");
        sql.append(" where c.gopay_int_txn_cd in ('00606')  and c.txn_sta_cd ='20000'") ;
        sql.append(" and c.main_recv_acct = '").append(acctId).append("'");
        sql.append(" and c.main_recv_cust_id = '").append(custId).append("'");
        sql.append(" and c.gateway_source = '22'       ");
        sql.append(" and c.mer_txn_tm >= ");
        sql.append(" to_timestamp('").append(beginTime).append("','yyyy-MM-dd HH24:MI:ss')");
        sql.append(" and c.mer_txn_tm <=");
        sql.append(" to_timestamp('").append(endTime).append("','yyyy-MM-dd HH24:MI:ss')");
        return sql.toString();
    }
	
	/**
	 * @Description: 构建分账明细查询SQL
	 * @param custId
	 * @param acctId
	 * @param beginTime
	 * @param endTime
	 * @return 
	 * @author liuxuyang
	 */
	private String buildSplitDetailsSql(String custId, String acctId, String beginTime, String endTime,String acctType) {
		StringBuilder sql = new StringBuilder();
		
		if (StringUtils.equals(acctType, AcctProd.SUBCLOUD.name())) {
			sql.append(" select rel.agree_id,");
		} else {
			sql.append(" select mo.main_recv_acct,");
		}
        sql.append(" mo.gopay_order_id,");
        sql.append(" mo.mer_txn_amt,  ");
        sql.append(" mo2.gopay_order_id,       ");
        sql.append(" mo2.mer_txn_amt, ") ;
        sql.append(" d.other_recv_time, ") ;
        sql.append(" d.sub_acct_finish_time ") ;
        sql.append(" from cps_gen_main_order mo ") ;
        sql.append(" left join cps_gen_main_order mo2 on mo.relevance_gopay_order_id = mo2.gopay_order_id ") ;
        sql.append(" left join cps_gen_receipt_order d on mo2.gopay_order_id = d.gopay_order_id ") ;
        if (StringUtils.equals(acctType, AcctProd.SUBCLOUD.name())) {
			sql.append(" left join cloud_cust_acct_rel rel on rel.acct_id = mo.main_recv_acct ");
		}
        sql.append(" where mo.main_recv_acct = '").append(acctId).append("'");
        sql.append(" and mo.main_recv_cust_id = '").append(custId).append("'");
        sql.append(" and mo.gopay_int_txn_cd = '00606'       ");
        sql.append(" and mo.txn_sta_cd = '20000'       ");
        sql.append(" and mo.gateway_source = '22'       ");
        sql.append(" and mo.mer_txn_tm >= ");
        sql.append(" to_timestamp('").append(beginTime).append("','yyyy-MM-dd HH24:MI:ss')");
        sql.append(" and mo.mer_txn_tm <= ");
        sql.append(" to_timestamp('").append(endTime).append("','yyyy-MM-dd HH24:MI:ss')");
        
		return sql.toString();
	}
	
	/**
	 * @Description: 收款类对账文件汇总
	 * @param custId
	 * @param acctId
	 * @param beginTime
	 * @param endTime
	 * @return 
	 * @author liuxuyang
	 */
	private String buildRecvTotalSql(String custId, String acctId, String beginTime, String endTime) {
        StringBuilder sql = new StringBuilder();
        sql.append(" select count(*),");
        sql.append(" sum(c.mer_txn_amt) as totalAmt,");
        sql.append(" sum(case when c.order_fee_payer ='02' then c.total_order_fee_amt else 0.00 end) as totalFeeAmt ");
        sql.append(" from cps_gen_main_order c       ");
        sql.append(" where c.gopay_int_txn_cd in ('00100')  and c.txn_sta_cd ='20000'") ;
        sql.append(" and c.gateway_source = '22'       ");
        sql.append(" and ((c.main_recv_acct = '").append(acctId).append("'");
        sql.append(" and c.main_recv_cust_id = '").append(custId).append("')  ");
        sql.append(" or (c.remark1 = '").append(acctId).append("'");
        sql.append(" and c.mer_id = '").append(custId).append("'))  ");
        sql.append(" and c.mer_txn_tm>= ");
        sql.append(" to_timestamp('").append(beginTime).append("','yyyy-MM-dd HH24:MI:ss')");
        sql.append(" and c.mer_txn_tm <=");
        sql.append(" to_timestamp('").append(endTime).append("','yyyy-MM-dd HH24:MI:ss')");
        return sql.toString();
    }
	
	/**
	 * @Description: 收款类对账文件明细 
	 * @param custId
	 * @param acctId
	 * @param beginTime
	 * @param endTime
	 * @return 
	 * @author liuxuyang
	 */
	private String buildRecvDetailsSql(String custId, String acctId, String beginTime, String endTime) {
        StringBuilder sql = new StringBuilder();
        sql.append(" select c.gopay_order_id,                   ");
        sql.append("        c.mer_order_id,                     ");
        sql.append("        c.gopay_int_txn_cd,                 ");
        sql.append("        c.mer_txn_tm,                       ");
        sql.append("        c.tran_finish_time,                 ");
        sql.append("        c.mer_txn_amt,                      ");
        sql.append(" (case when c.order_fee_payer ='02' then c.order_real_fee_amt else 0.00 end) as order_real_fee_amt,");
        sql.append("        c.pay_channel,                       ");
        sql.append("        r.other_recv_sytle,                      ");
        sql.append("        r.other_recv_status                     ");
        sql.append(" from cps_gen_main_order c       ");
        sql.append(" left join cps_gen_receipt_order r on c.gopay_order_id = r.gopay_order_id      ");
        sql.append(" where c.gopay_int_txn_cd in ('00100')  and c.txn_sta_cd ='20000'") ;
        sql.append(" and c.gateway_source = '22'       ");
        sql.append(" and ((c.main_recv_acct = '").append(acctId).append("'");
        sql.append(" and c.main_recv_cust_id = '").append(custId).append("')  ");
        sql.append(" or (c.remark1 = '").append(acctId).append("'");
        sql.append(" and c.mer_id = '").append(custId).append("'))  ");
        sql.append(" and c.mer_txn_tm>= ");
        sql.append(" to_timestamp('").append(beginTime).append("','yyyy-MM-dd HH24:MI:ss')");
        sql.append(" and c.mer_txn_tm <=");
        sql.append(" to_timestamp('").append(endTime).append("','yyyy-MM-dd HH24:MI:ss')");
        return sql.toString();
    }
	
	/**
	 * @Description: 退款类对账文件汇总 
	 * @param custId
	 * @param acctId
	 * @param beginTime
	 * @param endTime
	 * @return 
	 * @author liuxuyang
	 */
	private String buildRefundTotalSql(String custId, String acctId, String beginTime, String endTime) {
        StringBuilder sql = new StringBuilder();
        sql.append(" select count(*),");
        sql.append(" sum(c.mer_txn_amt) as totalAmt,");
        sql.append(" sum(case when c.order_fee_payer ='01' then c.total_order_fee_amt else 0.00 end) as totalFeeAmt ");
        sql.append(" from cps_gen_main_order c       ");
        sql.append(" where c.gopay_int_txn_cd in ('01200') and c.txn_sta_cd ='20000' ") ;
        sql.append(" and c.gateway_source = '22'       ");
        sql.append(" and ((c.pay_acct = '").append(acctId).append("'");
        sql.append(" and c.pay_cust_id = '").append(custId).append("' )");
        sql.append("  or (c.remark1 = '").append(acctId).append("'");
        sql.append(" and c.mer_id = '").append(custId).append("' ))");
        sql.append(" and c.mer_txn_tm >= ");
        sql.append(" to_timestamp('").append(beginTime).append("','yyyy-MM-dd HH24:MI:ss')");
        sql.append(" and c.mer_txn_tm <=");
        sql.append(" to_timestamp('").append(endTime).append("','yyyy-MM-dd HH24:MI:ss')");
        return sql.toString();
    }
	
	/**
	 * @Description: 退款对账文件明细
	 * @param custId
	 * @param acctId
	 * @param beginTime
	 * @param endTime
	 * @return 
	 * @author liuxuyang
	 */
	private String buildRefundDetailsSql(String custId, String acctId, String beginTime, String endTime) {
        StringBuilder sql = new StringBuilder();
        sql.append(" select c.gopay_order_id,                   ");
        sql.append("        c.mer_order_id,                     ");
        sql.append("        c.relevance_gopay_order_id,         ");
        sql.append("        c.gopay_int_txn_cd,                 ");
        sql.append("        c.mer_txn_tm,                       ");
        sql.append("        c.tran_finish_time,                 ");
        sql.append("        c.mer_txn_amt,                      ");
        sql.append(" (case when c.order_fee_payer ='01' then c.order_real_fee_amt else 0.00 end) as order_real_fee_amt,");
        sql.append("        c.pay_channel                       ");
        sql.append(" from cps_gen_main_order c       ");
        sql.append(" where c.gopay_int_txn_cd in ('01200') and c.txn_sta_cd ='20000' ") ;
        sql.append(" and c.gateway_source = '22'       ");
        sql.append(" and ((c.pay_acct = '").append(acctId).append("'");
        sql.append(" and c.pay_cust_id = '").append(custId).append("' )");
        sql.append("  or (c.remark1 = '").append(acctId).append("'");
        sql.append(" and c.mer_id = '").append(custId).append("' ))");
        sql.append(" and c.mer_txn_tm>= ");
        sql.append(" to_timestamp('").append(beginTime).append("','yyyy-MM-dd HH24:MI:ss')");
        sql.append(" and c.mer_txn_tm <=");
        sql.append(" to_timestamp('").append(endTime).append("','yyyy-MM-dd HH24:MI:ss')");
        return sql.toString();
    }
	
	/**
	 * @Description: 删除文件或文件夹
	 * @param file 
	 * @author liuxuyang
	 */
	private void deleteFile(File file) {
        if (file.exists()) {
            if (file.isFile()) {
                file.delete();
            } else if (file.isDirectory()) {
                File[] files = file.listFiles();
                for (int i = 0;i < files.length;i ++) {
                    this.deleteFile(files[i]);
                }
                file.delete();//删除文件夹
            }
        }
    }
	
	/**
	 * @Description: 交易类型转换
	 * @param gopayIntTxnCd
	 * @return 
	 * @author liuxuyang
	 */
	private String translateGopayIntTxnCd(String gopayIntTxnCd) {
        String result = "";
        if(gopayIntTxnCd != null) {
            switch (gopayIntTxnCd) {
                case "00100" :
                    result = "网关支付";
                    break;
                case "01200" :
                    result = "收款退款";
                    break;
                case "00700" :
                    result = "付款到银行";
                    break;
                case "01812" :
                    result = "云账户转账";
                    break;
                case "00800" :
                    result = "提现";
                case "01202" :
                    result = "分账退款";
                    break;
            }
        }
        return result;
    }
	
	/**
	 * @Description: 分账类型翻译
	 * @param splitType
	 * @return 
	 * @author liuxuyang
	 */
	private String translateSplitType(String splitType) {
		String result = "";
        if(splitType != null) {
            switch (splitType) {
                case "1" :
                    result = "实时";
                    break;
                case "2" :
                    result = "延时";
                    break;
            }
        }
        return result;
	}
	
	/**
	 * @Description: 分账状态翻译
	 * @param splitStatus
	 * @return 
	 * @author liuxuyang
	 */
	private String translateSplitStatus(String splitStatus) {
		String result = "";
        if(splitStatus != null) {
            switch (splitStatus) {
                case "1" :
                    result = "待分账";
                    break;
                case "2" :
                    result = "分账成功";
                    break;
                case "3" :
                    result = "分账失败";
                    break;
                case "4" :
                    result = "分账进行中";
                    break;
            }
        }
        return result;
	}
	
	/**
	 * @Description: 将生成的文件上传到FTP
	 * @param localPath
	 * @param fileNameSet 
	 * @author liuxuyang
	 */
	private void uploadFileToFtp(String localPath,Set<String> fileNameSet){
        logger.info("上传FTP文件开始！！！！");
        logger.info("上传FTP的本地文件路径为："+localPath);
        String url = cloudFtpConfig.get("ftp.ip");
        int port = Integer.valueOf(cloudFtpConfig.get("ftp.port"));
        String username = cloudFtpConfig.get("ftp.username");
        String password = cloudFtpConfig.get("ftp.password");
        String ftpPath = cloudFtpConfig.get("ftp.upload.path");
        String encoding = "UTF-8";

        for(String fileName : fileNameSet) {
            logger.info("上传FTP的文件名称是：" + fileName);
            boolean isFtpSuccess = FtpUtil.uploadFile(url,port,username,password,ftpPath,fileName,localPath,fileName,encoding);
            logger.info("上传FTP文件是否成功："+ String.valueOf(isFtpSuccess));
        }
        logger.info("上传FTP文件结束！！！！");
    }
}
